package dp.dao.ibatis;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;

import com.github.pagehelper.Page;

import dp.dao.GenericDao;
import dp.dao.PagingList;
import dp.util.BeanUtil;

/**
 *
 * @author chensh
 */
public class GenericDaoImpl implements GenericDao{
    private static final String ORACLE = "oracle";
    private static final String DB2 = "db2";
    private static final String SQLSERVER = "microsoft sql server";
    private static final String MYSQL = "mysql";
    private static final String POSTGRESQL = "postgresql";
    //数据库类型（oracle、db2、、、）
    private String databaseName = null;
    //一次批量处理的记录数
    private int batchSize = 500;
    
    public int getBatchSize() {
		return batchSize;
	}

	public void setBatchSize(int batchSize) {
		if(batchSize<1){
			throw new IllegalArgumentException("batchSize不能小于1");
		}
		this.batchSize = batchSize;
	}

	protected SqlSessionTemplate sqlSessionTemplate;  

	public SqlSessionTemplate getSqlSessionTemplate() {
		return sqlSessionTemplate;
	}

	public void setSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) {
		this.sqlSessionTemplate = sqlSessionTemplate;
	}

	//返回数据库类型，小写
    public String getDatabaseName(){
    	if(databaseName==null){
    		try {
    			Connection conn = getConnection();
    			DatabaseMetaData databaseMetaData = conn.getMetaData();
    			databaseName = databaseMetaData.getDatabaseProductName();
    			databaseName = databaseName.toLowerCase();
    		}
    		catch (SQLException e) {
    		    databaseName = "database_unknown";
    			throw new RuntimeException(e);
    		}
    	}
    	return databaseName;
    }
    
    public boolean isOracle() {
		return ORACLE.equals(getDatabaseName());
	}

	public boolean isDB2() {
		return DB2.equals(getDatabaseName());
	}

	public boolean isSQLServer() {
		return SQLSERVER.equals(getDatabaseName());
	}

	public boolean isMySql() {
		return MYSQL.equals(getDatabaseName());
	}
	
	public boolean isPostgreSql() {
		return POSTGRESQL.equals(getDatabaseName());
	}
	
	public boolean hasTable(String tableName) {
        boolean result = false;
        try {
        	Connection conn = getConnection();
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet set = meta.getTables(null, null, tableName, null);
            if (set.next()) {
                result = true;
            }
        } catch (SQLException e) {
        	throw new RuntimeException(e);
        }
        return result;
    }
	
	public Connection getConnection() throws SQLException{
		//return sqlSessionTemplate.getConfiguration().getEnvironment().getDataSource().getConnection();
		return SqlSessionUtils.getSqlSession(
				sqlSessionTemplate.getSqlSessionFactory(), sqlSessionTemplate.getExecutorType(),
				sqlSessionTemplate.getPersistenceExceptionTranslator()).getConnection();
	}
    
    public void insert(String statement, Object parameterObject) {
    	sqlSessionTemplate.insert(statement, parameterObject);
    }

    public int update(String statement, Object parameterObject) {
        return sqlSessionTemplate.update(statement, parameterObject);
    }

    public int delete(String statement, Object parameterObject) {
        return sqlSessionTemplate.delete(statement, parameterObject);
    }

    /**
     * 读取一个对象,判断从ibatis返回的对象是否是Map，如果是Map则把key全部转换成javabean的属性写法，比如USER_CD转换成userCd
     * @param statement sqlid
     * @param parameterObject 参数
     * @param clazz 返回类型
     * @return 查询结果
     */
    public <T> T queryForObject(String statement, Object parameterObject, Class<T> clazz) {
    	return queryForObject(statement, parameterObject, clazz, true);
    }
    
    public <T> T queryForObject(String statement, Object parameterObject, Class<T> clazz, boolean transformMapFieldname) {
        Object obj =  sqlSessionTemplate.selectOne(statement, parameterObject);
        if(transformMapFieldname && obj !=null && obj instanceof Map){
        	return (T)mapUnderscoreToCamelCase((Map)obj);
        }
        else{
            return (T)obj;
        }
    }

    /**
     * 读取一系列对象,如果是Map则把key全部转换成javabean的属性写法，比如USER_CD转换成userCd
     * @param statement sqlid
     * @param parameterObject 参数
     * @param clazz 返回类型
     * @return 查询结果
     */
    public <T> List<T> queryForList(String statement, Object parameterObject, Class<T> clazz) {
        return queryForList(statement, parameterObject, clazz, true);
    }
    
    public <T> List<T> queryForList(String statement, Object parameterObject, Class<T> clazz, boolean transformMapFieldname) {
        List list = sqlSessionTemplate.selectList(statement, parameterObject);
        //当list的第一个对象是Map时才转换
        if(transformMapFieldname && list.size()>0 && (list.get(0) instanceof Map )){
            List t = new ArrayList(list.size());
            Map oldMap = null;
            for(int i=0, size=list.size(); i<size; i++){
                oldMap = (Map)list.get(i);
                if(oldMap!=null){
                    t.add(mapUnderscoreToCamelCase(oldMap));
                }
            }
            return (List<T>)t;
        }
        else{
            return (List<T>)list;
        }
    }
    
    
    /**
     * 分页查询，如果是Map则把key全部转换成javabean的属性写法，用驼峰命名，比如USER_CD转换成userCd
     * @param statement sqlid
     * @param param 参数
     * @param pageIndex 第几页
     * @param limit 一页的记录数
     * @param clazz 返回类型
     * @return 查询结果
     */
    public <T> PagingList<T> queryForPagingList(String statement, Object parameterObject, int pageIndex, int limit, Class<T> clazz){
    	return queryForPagingList(statement, parameterObject, pageIndex, limit, clazz, true);
    }
    
    public <T> PagingList<T> queryForPagingList(String statement, Object parameterObject, int pageIndex, int limit, Class<T> clazz, boolean transformMapFieldname){
        List list = sqlSessionTemplate.selectList(statement, parameterObject, new RowBounds(pageIndex, limit));
        //取总记录数
        Page page = (Page)list;
        
        //当list的第一个对象是Map时才转换
        if(transformMapFieldname && list.size()>0 && (list.get(0) instanceof Map )){
            List t = new ArrayList(list.size());
            Map oldMap = null;
            for(int i=0, size=list.size(); i<size; i++){
                oldMap = (Map)list.get(i);
                if(oldMap!=null){
                    t.add(mapUnderscoreToCamelCase(oldMap));
                }
            }
            return new PagingList<T>(page.getTotal(), t, page.getPages(), page.getPageNum(), page.getPageSize()); 
        }
        else{
        	//构造PagingList返回
        	return new PagingList<T>(page.getTotal(), list, page.getPages(), page.getPageNum(), page.getPageSize()); 
        }
    }
    
    /**
     * 批量插入
     * @param namespace
     * @param statement
     * @param list
     */
    public void batchInsert(final String statement, final List<?> list){
    	SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
    	try{
    		for(int i=0, size=list.size();i<size;i++)
            {    
        		session.insert(statement, list.get(i));
                if (i%batchSize==0 || i==size-1) {
                	session.commit();
                	session.clearCache();
                }
            }
    	}
    	catch(Exception e){
    		session.rollback();
    		throw new RuntimeException(e);
    	}
    	finally{
    		session.close();
    	}
    }
    
    /**
     * 批量删除
     * @param namespace
     * @param statement
     * @param list
     */
    public void batchDelete(final String statement, final List<?> list){
    	SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
    	try{
    		for(int i=0, size=list.size();i<size;i++)
            {    
        		session.delete(statement, list.get(i));
                if (i%batchSize==0 || i==size-1) {
                	session.commit();
                	session.clearCache();
                }
            }
    	}
    	catch(Exception e){
    		session.rollback();
    		throw new RuntimeException(e);
    	}
    	finally{
    		session.close();
    	}
    }
    
    /**
     * 批量修改
     * @param namespace
     * @param statement
     * @param list
     */
    public void batchUpdate(final String statement, final List<?> list){
    	SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
    	try{
    		for(int i=0, size=list.size();i<size;i++)
            {    
        		session.update(statement, list.get(i));
                if (i%batchSize==0 || i==size-1) {
                	session.commit();
                	session.clearCache();
                }
            }
    	}
    	catch(Exception e){
    		session.rollback();
    		throw new RuntimeException(e);
    	}
    	finally{
    		session.close();
    	}
    }

    public void inserBySQL(String sql){
        this.insert("common.insertBySQL", sql);
    }
    public int updateBySQL(String sql){
        return this.update("common.updateBySQL", sql);
    }
    public int deleteBySQL(String sql){
        return this.delete("common.deleteBySQL", sql);
    }
    public Map queryForObjectBySQL(String sql){
        return this.queryForObject("common.selectBySQL", sql, Map.class);
    }
    public List<Map> queryForListBySQL(String sql){
        return this.queryForList("common.selectBySQL", sql, Map.class);
    }

    public void batchInsertBySQL(List<String> sql) {
        this.batchInsert("common.insertBySQL", sql);
    }

    public void batchUpdateBySQL(List<String> sql) {
        this.batchInsert("common.updateBySQL", sql);
    }

    public void batchDeleteBySQL(List<String> sql) {
        this.batchInsert("common.deleteBySQL", sql);
    }
    
    /**
     * 将Map的key转成驼峰命名的方式
     * @param oldMap
     * @return
     */
    private Map<String,Object> mapUnderscoreToCamelCase(Map<String,Object> oldMap){
    	Map<String,Object> newMap = null;
    	if(oldMap!=null){
        	newMap = new HashMap(oldMap.size());
            //循环已有的Map，取出Key转换到javabean属性名
            Iterator iterator = oldMap.entrySet().iterator();
            while (iterator.hasNext()) {
                Map.Entry entry = (Map.Entry) iterator.next();
                newMap.put(BeanUtil.filedNameToJavaName(entry.getKey().toString()), entry.getValue());
            }
        }
    	return newMap;
    }
}
